﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace CommHelper
{

    public class DataLog
    {

        public List<string> Exinclude = new List<string>();
        public List<string> ExincludeFrom = new List<string>();

        private void ExincludeInit()
        {
            Exinclude.Add("ValidPwd");
            Exinclude.Add("WriteUserLog");
        }

        private void ExincludeFromInit()
        {
            ExincludeFrom.Add("OSSService");
        }

        public DataLog(string from, int uid)
        {
            From = from;
            OpUid = uid;
            ExincludeInit();
            ExincludeFromInit();
            if (ExincludeFrom.Contains(from))
            {
                SaveLog = false;
            }
        }

        public DataLog(string from, int uid, bool save)
        {
            From = from;
            OpUid = uid;
            SaveLog = save;
            ExincludeInit();
            ExincludeFromInit();
            if (ExincludeFrom.Contains(from))
            {
                SaveLog = false;
            }
        }

        public bool SaveLog = true;

        public string From { set; get; }
        public int OpUid { set; get; }
    }

    public class DataManager
    {
        public DataLog Log = new DataLog("unknow", 0);

        private string _ConnectionString = ConfigManager.GetAppSettings("ConnectString");
        public string ConnectKey
        {
            get
            {
                return _ConnectionString;
            }
            set
            {
                _ConnectionString = ConfigManager.GetConnectionString(value);
            }
        }

        private string SqlText = "";

        public DataManager() { }

        public DataManager(DataLog dataLog)
        {
            Log = dataLog;
        }

        public DataManager(string DataBaseName)
        {
            ConnectKey = DataBaseName;
        }

        public void UserLog(Dictionary<string, string> Pas)
        {
            if (!Log.SaveLog)
            {
                return;
            }
            if (Log.Exinclude.Contains(Pas["ProcName"]))
            {
                return;
            }
            DataSet dsTmp = new DataSet();
            using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
            {
                if (null != sqlConnection && sqlConnection.State == ConnectionState.Closed)
                {
                    sqlConnection.Open();
                }

                SqlCommand cmd = new SqlCommand();
                //DateTime.Now.Millisecond
                SqlText = "System_UserLogs";

                foreach (KeyValuePair<string, string> kv in Pas)
                {
                    cmd.Parameters.Add(new SqlParameter(kv.Key, kv.Value));
                }

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = SqlText;
                cmd.Connection = sqlConnection;

                SqlDataAdapter da = new SqlDataAdapter
                {
                    SelectCommand = cmd
                };
                da.Fill(dsTmp);
            }
        }

        public void SqlTrack(String Url, String ProcName, String ProcParms)
        {
            using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
            {
                if (null != sqlConnection && sqlConnection.State == ConnectionState.Closed)
                {
                    sqlConnection.Open();
                }

                SqlCommand cmd = new SqlCommand();
                SqlText = "System_TrackSql";

                //foreach (KeyValuePair<string, string> kv in Pas)
                //{
                cmd.Parameters.Add(new SqlParameter("Url", Url));
                cmd.Parameters.Add(new SqlParameter("ProcName", ProcName));
                cmd.Parameters.Add(new SqlParameter("ProcParms", ProcParms));
                //cmd.Parameters.Add(new SqlParameter("@SysLogId", null)); 
                //cmd.Parameters.Add(new SqlParameter("@OpUid", null));
                //}

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = SqlText;
                cmd.Connection = sqlConnection;
                cmd.ExecuteNonQuery();
                //cmd.Parameters = 
                //SqlDataAdapter da = new SqlDataAdapter();
                //da.SelectCommand = cmd;

                //da.Fill(dsTmp);
            }
        }

        public void AddError(Dictionary<string, string> Pas)
        {
            using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
            {
                if (null != sqlConnection && sqlConnection.State == ConnectionState.Closed)
                {
                    sqlConnection.Open();
                }

                SqlCommand cmd = new SqlCommand();
                SqlText = "System_Error";

                foreach (KeyValuePair<string, string> kv in Pas)
                {
                    cmd.Parameters.Add(new SqlParameter(kv.Key, kv.Value));
                }

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = SqlText;
                cmd.Connection = sqlConnection;
                cmd.ExecuteNonQuery();
                //cmd.Parameters = 
                //SqlDataAdapter da = new SqlDataAdapter();
                //da.SelectCommand = cmd;

                //da.Fill(dsTmp);
            }
        }

        public DataToInfo SqlCmd(String Sql)
        {
            String LogID = Guid.NewGuid().ToString();
            try
            {
                DateTime StartTime = DateTime.Now;
                DataSet dsTmp = new DataSet();
                using (SqlConnection sqlConnection = new SqlConnection(this._ConnectionString))
                {
                    if (null != sqlConnection && sqlConnection.State == ConnectionState.Closed)
                    {
                        sqlConnection.Open();
                    }

                    SqlCommand cmd = new SqlCommand();
                    this.SqlText = Sql;
                    cmd.CommandText = this.SqlText;
                    cmd.Connection = sqlConnection;

                    //cmd.Parameters = 
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    da.Fill(dsTmp);

                    try
                    {
                        DateTime EndTime = DateTime.Now;

                        TimeSpan secondSpan = new TimeSpan(EndTime.Ticks - StartTime.Ticks);

                        Dictionary<String, String> LogDic = new Dictionary<string, string>();
                        LogDic.Add("ID", LogID);
                        LogDic.Add("Url", this.Log.From);
                        LogDic.Add("ProcName", Sql);
                        LogDic.Add("ProcParms", "");
                        LogDic.Add("CreatedBy", this.Log.OpUid.ToString());
                        LogDic.Add("UseSecond", secondSpan.TotalSeconds.ToString());
                        this.UserLog(LogDic);
                    }
                    catch (Exception ex)
                    {
                        Dictionary<String, String> dic = new Dictionary<string, string>();
                        dic.Add("ErrorApi", Sql);
                        dic.Add("ErrorApiParas", "");
                        dic.Add("Description", ex.StackTrace);
                        AddError(dic);
                    }
                }
                if (dsTmp.Tables != null && dsTmp.Tables.Count > 0)
                {
                    return JSONManager.DataTableParse(dsTmp.Tables[0]);
                }
                else
                {
                    return new DataToInfo();
                }
            }
            catch (Exception ex)
            {
                Dictionary<String, String> dic = new Dictionary<string, string>();
                dic.Add("ErrorApi", ex.StackTrace);
                dic.Add("ErrorApiParas", Sql);
                dic.Add("Description", ex.Message);
                AddError(dic);
                return null;
            }
        }

        public DataToInfo Procedure(string ProcName, string Json)
        {
            if (Json == "")
            {
                Json = "[]";
            }
            string LogID = Guid.NewGuid().ToString();

            try
            {
                DataSet dsTmp = new DataSet();
                DateTime StartTime = DateTime.Now;
                using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
                {
                    if (null != sqlConnection && sqlConnection.State == ConnectionState.Closed)
                    {
                        sqlConnection.Open();
                    }


                    SqlParameter[] prs = ConvertJsonToParameter(Json);

                    SqlCommand cmd = new SqlCommand
                    {
                        CommandTimeout = 300
                    };
                    SqlText = ProcName;

                    foreach (SqlParameter p in prs)
                    {
                        cmd.Parameters.Add(p);
                    }

                    if (!cmd.Parameters.Contains("@SysLogId"))
                    {
                        cmd.Parameters.Add(new SqlParameter("@SysLogId", LogID));
                    }
                    if (!cmd.Parameters.Contains("@OpUid"))
                    {
                        cmd.Parameters.Add(new SqlParameter("@OpUid", Log.OpUid));
                    }


                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = SqlText;
                    cmd.Connection = sqlConnection;

                    //cmd.Parameters = 
                    SqlDataAdapter da = new SqlDataAdapter
                    {
                        SelectCommand = cmd
                    };

                    da.Fill(dsTmp);

                    try
                    {
                        DateTime EndTime = DateTime.Now;

                        TimeSpan secondSpan = new TimeSpan(EndTime.Ticks - StartTime.Ticks);

                        Dictionary<string, string> LogDic = new Dictionary<string, string>
                        {
                            { "ID", LogID },
                            { "Url", Log.From },
                            { "ProcName", ProcName },
                            { "ProcParms", Json },
                            { "CreatedBy", Log.OpUid.ToString() },
                            { "UseSecond", secondSpan.TotalSeconds.ToString() }
                        };
                        UserLog(LogDic);
                    }
                    catch (Exception ex)
                    {
                        Dictionary<string, string> dic = new Dictionary<string, string>
                        {
                            { "ErrorApi", ProcName },
                            { "ErrorApiParas", Json },
                            { "Description", ex.StackTrace }
                        };
                        AddError(dic);
                    }

                }
                if (dsTmp.Tables != null && dsTmp.Tables.Count > 0)
                {
                    return JSONManager.DataTableParse(dsTmp.Tables[0]);
                }
                else
                {
                    return new DataToInfo();
                }
            }
            catch (Exception ex)
            {
                Dictionary<string, string> dic = new Dictionary<string, string>
                {
                    { "ErrorApi", ex.StackTrace },
                    { "ErrorApiParas", SqlText },
                    { "Description", ex.Message }
                };
                AddError(dic);
                throw ex;
            }
        }

        public DataToList ProcedureSet(string ProcName, string Json)
        {
            if (Json == "")
            {
                Json = "[]";
            }
            string LogID = Guid.NewGuid().ToString();

            try
            {
                DateTime StartTime = DateTime.Now;
                DataSet dsTmp = new DataSet();


                using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
                {
                    if (null != sqlConnection && sqlConnection.State == ConnectionState.Closed)
                    {
                        sqlConnection.Open();
                    }


                    SqlParameter[] prs = ConvertJsonToParameter(Json);

                    SqlCommand cmd = new SqlCommand
                    {
                        CommandTimeout = 300
                    };
                    SqlText = ProcName;
                    foreach (SqlParameter p in prs)
                    {
                        cmd.Parameters.Add(p);
                    }

                    if (!cmd.Parameters.Contains("@SysLogId"))
                    {
                        cmd.Parameters.Add(new SqlParameter("@SysLogId", LogID));
                    }
                    if (!cmd.Parameters.Contains("@OpUid"))
                    {
                        cmd.Parameters.Add(new SqlParameter("@OpUid", Log.OpUid));
                    }

                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = SqlText;
                    cmd.Connection = sqlConnection;

                    //cmd.Parameters = 
                    SqlDataAdapter da = new SqlDataAdapter
                    {
                        SelectCommand = cmd
                    };

                    da.Fill(dsTmp);


                    try
                    {
                        DateTime EndTime = DateTime.Now;

                        TimeSpan secondSpan = new TimeSpan(EndTime.Ticks - StartTime.Ticks);

                        Dictionary<string, string> LogDic = new Dictionary<string, string>
                        {
                            { "ID", LogID },
                            { "Url", Log.From },
                            { "ProcName", ProcName },
                            { "ProcParms", Json },
                            { "CreatedBy", Log.OpUid.ToString() },
                            { "UseSecond", secondSpan.TotalSeconds.ToString() }
                        };
                        UserLog(LogDic);
                    }
                    catch (Exception ex)
                    {
                        Dictionary<string, string> dic = new Dictionary<string, string>
                        {
                            { "ErrorApi", ProcName },
                            { "ErrorApiParas", Json },
                            { "Description", ex.StackTrace }
                        };
                        AddError(dic);
                    }
                }
                if (dsTmp.Tables != null && dsTmp.Tables.Count > 0)
                {
                    return JSONManager.DataSetParse(dsTmp);
                }
                else
                {
                    return new DataToList();
                }
            }
            catch (Exception ex)
            {
                Dictionary<string, string> dic = new Dictionary<string, string>
                {
                    { "ErrorApi", ex.StackTrace },
                    { "ErrorApiParas", SqlText },
                    { "Description", ex.Message }
                };
                AddError(dic);
                throw ex;
            }
        }
          

        private SqlParameter[] ConvertJsonToParameter(string Json)
        {
            List<Dictionary<string, string>> Parms = JSONManager.ListJsonParse(Json);
            //for(int i=0;i< Parms.Count; i++)
            //{
            //    SqlTrack(Parms[i]);
            //} 

            List<SqlParameter> pms = new List<SqlParameter>();
            bool SaveBase64 = false;
            for (int i = 0; i < Parms.Count; i++)
            {
                if (SaveBase64)
                {
                    break;
                }
                foreach (KeyValuePair<string, string> kv in Parms[i])
                {
                    if (kv.Key == "base64force")
                    {
                        SaveBase64 = true;
                        break;
                    }
                }
            }
            for (int i = 0; i < Parms.Count; i++)
            {
                foreach (KeyValuePair<string, string> kv in Parms[i])
                {
                    if (kv.Key == "base64force")
                    {
                        continue;
                    }

                    //Console.WriteLine(kv.Value);
                    //string Rx = @"^[1-9]\d*$";

                    //if (Regex.IsMatch(kv.Value, Rx) || kv.Value.Equals("0") || kv.Value.Equals("-1"))
                    //{ 
                    string Value = System.Net.WebUtility.UrlDecode(kv.Value)
                    .Replace("$*$*$", "'")
                    .Replace("$@$@$", "\"")
                    .Replace("$!$!$", "\\")
                    .Replace("%2b", "+")
                    .Replace("%3d", "=")
                    .Replace("%26", "&");

                    bool IsBase64 = false;
                    string ImgExt = "";
                    if (Value.StartsWith("data:image/jpeg;base64"))
                    {
                        IsBase64 = true;
                        ImgExt = "jpg";
                    }
                    if (Value.StartsWith("data:image/png;base64"))
                    {
                        IsBase64 = true;
                        ImgExt = "png";
                    }
                    if (Value.StartsWith("data:image/gif;base64"))
                    {
                        IsBase64 = true;
                        ImgExt = "gif";
                    }



                    //    IsBase64 = false;
                    //}

                    if (IsBase64)
                    {
                        if (SaveBase64)
                        {
                            Value = Value.Replace("base64;", "base64,").Replace(" ", "+");
                        }
                        else
                        {
                            Value = Value.Replace("base64;", "base64,");
                            string byteStr = Value;//data:image/jpg;base64,         
                            int delLength = byteStr.IndexOf(',') + 1;
                            string str = byteStr.Substring(delLength, byteStr.Length - delLength);

                            string txt = str.Replace(" ", "+");
                            byte[] arr = Convert.FromBase64String(txt);
                            MemoryStream ms = new MemoryStream(arr);
                            Bitmap bmp = new Bitmap(ms);

                            Image returnImage = bmp;
                            string Fname = Guid.NewGuid().ToString();

                            string SavePath = ConfigurationManager.AppSettings["WebPath"] + "\\Clips\\";
                            string Dt = DateTime.Now.ToString("yyyy-MM-dd");

                            string Folder = SavePath + "" + Dt;
                            if (!Directory.Exists(Folder))
                            {
                                Directory.CreateDirectory(Folder);
                            }

                            string FileName = Folder + "\\" + Fname + "." + ImgExt;
                            try
                            {
                                returnImage.Save(FileName, ImageFormat.Jpeg);
                                Value = "/Clips/" + Dt + "/" + Fname + "." + ImgExt;
                            }
                            catch
                            {
                            }
                        }
                    }

                    pms.Add(new SqlParameter("@" + kv.Key, Value == "" ? (object)DBNull.Value : Value));
                    //}
                    //else {
                    //    pms.Add(new SqlParameter("@" + kv.Key, "'" + kv.Value + "'"));
                    //}
                }
            }

            SqlParameter[] SqlParms = pms.ToArray();

            return SqlParms;
        }

        private string ConvertJsonToCondition_Insert(string Table, string Json, out SqlParameter[] SqlParms)
        {
            List<Dictionary<string, string>> Parms = JSONManager.ListJsonParse(Json);

            SqlParms = new SqlParameter[] { };
            List<SqlParameter> pms = new List<SqlParameter>();

            string Fields = "";
            string Values = "";

            for (int i = 0; i < Parms.Count; i++)
            {
                foreach (KeyValuePair<string, string> kv in Parms[i])
                {
                    Fields += kv.Key + ",";
                    Values += "@" + kv.Key + ",";

                    pms.Add(new SqlParameter("@" + kv.Key, kv.Value == "" ? (object)DBNull.Value : kv.Value));
                }
            }

            if (!string.IsNullOrEmpty(Fields))
            {
                Fields = Fields.Substring(0, Fields.Length - 1);
                Values = Values.Substring(0, Values.Length - 1);
            }


            SqlParms = pms.ToArray();

            string Result = "insert into " + Table + "(" + Fields + ") values(" + Values + ")   select @@identity ";

            return Result;
        }

    }
}
